//
//  DataDictionaryDataSource.swift
//  travelMapMvvm-swift2
//
//  Created by green on 15/11/2.
//  Copyright © 2015年 travelMapMvvm-swift2. All rights reserved.
//

import Foundation

public class DataDictionaryDataSource: DataDictionaryDataSourceProtocol {
    
    public class func shareInstance()->DataDictionaryDataSourceProtocol{
        struct YRSingleton{
            static var predicate:dispatch_once_t = 0
            static var instance:DataDictionaryDataSource? = nil
        }
        dispatch_once(&YRSingleton.predicate,{
            YRSingleton.instance=DataDictionaryDataSource()
        })
        return YRSingleton.instance!
    }
    
    // MARK: - 初始化数据字典列表
    
    public func setUpDataDictionaryList() {
        
        // create table !!!
        
        if !FMDBManager.shareInstance().database.open() {
            print("Unable to open database")
            return
        }
        
        let finalSql = "CREATE TABLE IF NOT EXISTS DATA_DICTIONARY (DATA_DICTIONARY_ID INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL , DATA_DICTIONARY_CODE VARCHAR(30), DATA_DICTIONARY_NAME VARCHAR(30),DATA_DICTIONARY_TYPE VARCHAR(30));"
        
        if !FMDBManager.shareInstance().database.executeStatements(finalSql) {
            print("Unable Create SetUpDataDictionaryList")
        }
        
        FMDBManager.shareInstance().database.close()
        
        // insert !!!
        insertStrategyThemeList()
        insertStrategyTypeList()
        insertStrategyOrderList()
        insertMonthList()
        insertPOILevelList()
        insertPOITypeList()
        
        FMDBManager.shareInstance().database.close()
    }
    
    /**
     增加攻略主题
     */
    private func insertStrategyThemeList() {
        
        if queryDataDictionaryCount(.StrategyTheme) == 0 {
            
            if !FMDBManager.shareInstance().database.open() {
                print("Unable to open database")
                return
            }
            
            let rawValue = DataDictionaryEnum.StrategyTheme.rawValue
            let sql =
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"9H\",\"户外\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"9I\",\"文化\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"9J\",\"休闲\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"9E\",\"美食\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"9F\",\"购物\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G0\",\"观赏\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G1\",\"节庆\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G2\",\"自驾\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G3\",\"必备\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G4\",\"摄影\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G5\",\"建筑\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G6\",\"家庭\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G7\",\"星旅行\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G8\",\"滑雪\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"G9\",\"潜水\",\"\(rawValue)\");"
            
            if !FMDBManager.shareInstance().database.executeStatements(sql) {
                print("Unable Insert StrategyThemeList")
            }
            
            FMDBManager.shareInstance().database.close()
        }
    }
    
    /**
     增加攻略类型
     */
    private func insertStrategyTypeList() {
        
        if queryDataDictionaryCount(.StrategyType) == 0 {
            
            if !FMDBManager.shareInstance().database.open() {
                print("Unable to open database")
                return
            }
            
            let rawValue = DataDictionaryEnum.StrategyType.rawValue
            let sql =
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"1\",\"综合\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"2\",\"美食\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"3\",\"参观\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"4\",\"购物\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"5\",\"娱乐\",\"\(rawValue)\");"
            
            if !FMDBManager.shareInstance().database.executeStatements(sql) {
                print("Unable Insert StrategyTypeList")
            }
            
            FMDBManager.shareInstance().database.close()
        }
    }
    
    /**
     增加攻略排序
     */
    private func insertStrategyOrderList() {
        
        if queryDataDictionaryCount(.StrategyOrder) == 0 {
            
            if !FMDBManager.shareInstance().database.open() {
                print("Unable to open database")
                return
            }
            
            let rawValue = DataDictionaryEnum.StrategyOrder.rawValue
            let sql =
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"1\",\"排名升序\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"2\",\"点击数量降序\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"3\",\"评分降序\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"4\",\"更新时间降序\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"5\",\"下载量降序\",\"\(rawValue)\");"
            
            if !FMDBManager.shareInstance().database.executeStatements(sql) {
                print("Unable Insert StrategyOrderList")
            }
            
            FMDBManager.shareInstance().database.close()
        }
    }
    
    /**
     增加目的地类型
     */
    private func insertPOITypeList() {
        
        if queryDataDictionaryCount(.POIType) == 0 {
            
            if !FMDBManager.shareInstance().database.open() {
                print("Unable to open database")
                return
            }
            
            let rawValue = DataDictionaryEnum.POIType.rawValue
            let sql =
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"1\",\"景点\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"2\",\"酒店\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"3\",\"美食\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"4\",\"购物\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"5\",\"活动\",\"\(rawValue)\");"
            
            if !FMDBManager.shareInstance().database.executeStatements(sql) {
                print("Unable Insert POITypeList")
            }
            
            FMDBManager.shareInstance().database.close()
        }
    }
    
    /**
     增加目的地星级
     */
    private func insertPOILevelList() {
        
        if queryDataDictionaryCount(.POILevel) == 0 {
            
            if !FMDBManager.shareInstance().database.open() {
                print("Unable to open database")
                return
            }
            
            let rawValue = DataDictionaryEnum.POILevel.rawValue
            let sql =
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"1\",\"一个星\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"2\",\"二个星\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"3\",\"三个星\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"4\",\"四个星\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"5\",\"五个星\",\"\(rawValue)\");"
            
            if !FMDBManager.shareInstance().database.executeStatements(sql) {
                print("Unable Insert POILevelList")
            }
            
            FMDBManager.shareInstance().database.close()
        }
    }
    
    /**
     增加月份列表
     */
    private func insertMonthList() {
        
        if queryDataDictionaryCount(.Month) == 0 {
            
            if !FMDBManager.shareInstance().database.open() {
                print("Unable to open database")
                return
            }
            
            let rawValue = DataDictionaryEnum.Month.rawValue
            let sql =
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"1\",\"一月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"2\",\"二月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"3\",\"三月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"4\",\"四月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"5\",\"五月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"6\",\"六月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"7\",\"七月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"8\",\"八月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"9\",\"九月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"10\",\"十月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"11\",\"十一月\",\"\(rawValue)\");" +
                "INSERT INTO DATA_DICTIONARY (DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE) VALUES (\"12\",\"十二月\",\"\(rawValue)\");"
            
            if !FMDBManager.shareInstance().database.executeStatements(sql) {
                print("Unable Insert MonthList")
            }
            
            FMDBManager.shareInstance().database.close()
        }
    }
    
    // MARK: - 查询数据字典列表
    
    public func queryDataDictionaryList(dataDictionaryType: DataDictionaryEnum) -> [DataDictionaryModel] {
        
        var resultList = [DataDictionaryModel]()
        
        if !FMDBManager.shareInstance().database.open() {
            print("Unable to open database")
            return resultList
        }
        
        let sql = "SELECT DATA_DICTIONARY_ID, DATA_DICTIONARY_CODE, DATA_DICTIONARY_NAME, DATA_DICTIONARY_TYPE FROM DATA_DICTIONARY WHERE DATA_DICTIONARY_TYPE = ?"
        if let rs = FMDBManager.shareInstance().database.executeQuery(sql, withArgumentsInArray: [dataDictionaryType.rawValue]) {
            
            while rs.next() {
                
                let model = DataDictionaryModel()
                
                model.dataDictionaryId   = Int(rs.intForColumn("DATA_DICTIONARY_ID"))
                model.dataDictionaryCode = rs.stringForColumn("DATA_DICTIONARY_CODE")
                model.dataDictionaryName = rs.stringForColumn("DATA_DICTIONARY_NAME")
                model.dataDictionaryType = dataDictionaryType
                
                resultList.append(model)
            }
        } else {
            print("SELECT failed: \(FMDBManager.shareInstance().database.lastErrorMessage())")
        }
        
        FMDBManager.shareInstance().database.close()
        
        
        return resultList
    }
    
    // MARK: - 查询数据字典列表记录数
    
    public func queryDataDictionaryCount(dataDictionaryType: DataDictionaryEnum) -> Int {
        
        if !FMDBManager.shareInstance().database.open() {
            print("Unable to open database")
            return 0
        }
        
        let s = FMDBManager.shareInstance().database.executeQuery("SELECT COUNT(*) FROM DATA_DICTIONARY WHERE DATA_DICTIONARY_TYPE = ? ", dataDictionaryType.rawValue)
        
        if let s=s where s.next() {
            
            return Int(s.intForColumnIndex(0))
        }
        
        FMDBManager.shareInstance().database.close()
        
        return 0
    }
    
    // MARK: - 清空数据字典列表数据
    public func clearDataDictionaryList() {
        
        if !FMDBManager.shareInstance().database.open() {
            print("Unable to open database")
            return
        }
        
        FMDBManager.shareInstance().database.executeStatements("DELETE FROM DATA_DICTIONARY")
        
        FMDBManager.shareInstance().database.close()
        
    }
    
    // MARK: - 统计数据字典列表记录总和
    
    public func queryDataDictionaryTotalCount() -> Int {
        
        
        if !FMDBManager.shareInstance().database.open() {
            print("Unable to open database")
            return 0
        }
        
        let s = FMDBManager.shareInstance().database.executeQuery("SELECT COUNT(*) FROM DATA_DICTIONARY")
        
        if let s=s where s.next() {
            
            return Int(s.intForColumnIndex(0))
        }
        
        FMDBManager.shareInstance().database.close()
        
        return 0
    }
}